package dao.search;

import bean.search.ClassSearch;
import util.C3p0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 班级查询数据交互实现类
 **/

public class ClassSearchDao {
    public List<ClassSearch> classSearch(String cName){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ClassSearch> cs = null;

        String sql = "select c_name,c_number,s_number,s_name,s_sex,s_tel,r_number from t_class\n" +
                "left join t_student on t_class.c_number = t_student.s_c_number\n" +
                "left join t_room on t_student.s_r_number = t_room.r_number where c_name = ?;";

        try {
            conn = C3p0Util.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,cName);
            rs = ps.executeQuery();
            while(rs.next()) {
                if (cs == null) {
                    cs = new ArrayList<ClassSearch>();
                }
                ClassSearch classSearch = new ClassSearch();
                classSearch.setcName(rs.getString("c_name"));
                classSearch.setcNumber(rs.getInt("c_number"));
                classSearch.setsNumber(rs.getInt("s_number"));
                classSearch.setsName(rs.getString("s_name"));
                classSearch.setsSex(rs.getString("s_sex"));
                classSearch.setsTel(rs.getInt("s_tel"));
                classSearch.setrNumber(rs.getInt("r_number"));


                cs.add(classSearch);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return cs;
    }
}
